USE [Lottery]
GO
/****** Object:  Table [dbo].[AdminUser]    Script Date: 2024-11-06 14:04:07 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[AdminUser](
	[Identifier] [int] IDENTITY(1,1) NOT NULL,
	[AdminName] [nvarchar](50) NULL,
	[AdminPwd] [nvarchar](50) NULL,
 CONSTRAINT [PK_AdminUser] PRIMARY KEY CLUSTERED 
(
	[Identifier] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[LotteryPerson]    Script Date: 2024-11-06 14:04:07 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[LotteryPerson](
	[Identifier] [int] IDENTITY(1,1) NOT NULL,
	[FullName] [nvarchar](50) NULL,
	[Avatar] [nvarchar](max) NULL,
	[Company] [nvarchar](50) NULL,
	[Department] [nvarchar](50) NULL,
	[Phone] [nvarchar](50) NULL,
	[IsParticipator] [int] NULL,
	[AddDate] [datetime] NULL,
	[AddUser] [nvarchar](50) NULL,
	[SignInTime] [datetime] NULL,
 CONSTRAINT [PK_LotteryPerson] PRIMARY KEY CLUSTERED 
(
	[Identifier] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE [dbo].[LotteryPerson] ADD  CONSTRAINT [DF_LotteryPerson_IsParticipator]  DEFAULT ((2)) FOR [IsParticipator]
GO
/****** Object:  StoredProcedure [dbo].[LotteryPersonCreate]    Script Date: 2024-11-06 14:04:07 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
 CREATE PROCEDURE [dbo].[LotteryPersonCreate] 
	@Identifier int=NULL,
	@IsParticipator int=NULL,
	@AddDate datetime=NULL,
	@SignInTime datetime=NULL,
	@FullName nvarchar(50)=NULL,
	@Avatar nvarchar(MAX)=NULL,
	@Company nvarchar(50)=NULL,
	@Department nvarchar(50)=NULL,
	@Phone nvarchar(50)=NULL,
	@AddUser nvarchar(50)=NULL 
 AS 
 BEGIN  
 	INSERT INTO LotteryPerson(IsParticipator,AddDate,SignInTime,FullName,Avatar,Company,Department,Phone,AddUser)
 	VALUES(@IsParticipator,@AddDate,@SignInTime,@FullName,@Avatar,@Company,@Department,@Phone,@AddUser) 
 return @@identity
 END 
GO
/****** Object:  StoredProcedure [dbo].[LotteryPersonDelete]    Script Date: 2024-11-06 14:04:07 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
 CREATE PROCEDURE [dbo].[LotteryPersonDelete] 
	@Identifier int=NULL,
	@IsParticipator int=NULL,
	@AddDate datetime=NULL,
	@SignInTime datetime=NULL,
	@FullName nvarchar(50)=NULL,
	@Avatar nvarchar(MAX)=NULL,
	@Company nvarchar(50)=NULL,
	@Department nvarchar(50)=NULL,
	@Phone nvarchar(50)=NULL,
	@AddUser nvarchar(50)=NULL 
 AS 
 BEGIN 
 	DELETE LotteryPerson
 	WHERE 
	Identifier=@Identifier
	 return @Identifier 
	END
GO
/****** Object:  StoredProcedure [dbo].[LotteryPersonSelect]    Script Date: 2024-11-06 14:04:07 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
 CREATE PROCEDURE [dbo].[LotteryPersonSelect] 
	@PageSize int=0,
	@CurrentPage int=1, 
	@Identifier int=NULL,
	@IsParticipator int=NULL,
	@AddDate datetime=NULL,
	@SignInTime datetime=NULL,
	@FullName nvarchar(50)=NULL,
	@Avatar nvarchar(MAX)=NULL,
	@Company nvarchar(50)=NULL,
	@Department nvarchar(50)=NULL,
	@Phone nvarchar(50)=NULL,
	@AddUser nvarchar(50)=NULL 
 AS  
	
	IF NOT @FullName IS NULL  BEGIN SET @FullName='%'+@FullName+'%' END

	IF NOT @Avatar IS NULL  BEGIN SET @Avatar='%'+@Avatar+'%' END

	IF NOT @Company IS NULL  BEGIN SET @Company='%'+@Company+'%' END

	IF NOT @Department IS NULL  BEGIN SET @Department='%'+@Department+'%' END

	IF NOT @Phone IS NULL  BEGIN SET @Phone='%'+@Phone+'%' END

	IF NOT @AddUser IS NULL  BEGIN SET @AddUser='%'+@AddUser+'%' END
 
	IF @PageSize>0 
	BEGIN
		DECLARE @SumCount int
		DECLARE @TotalPage int

		SELECT Identifier,IsParticipator,AddDate,SignInTime,FullName,Avatar,Company,Department,Phone,AddUser 
			INTO #temp 
		FROM LotteryPerson 
		WHERE 1=1 AND 
		(@Identifier IS NULL OR Identifier=@Identifier)
		AND 
		(@IsParticipator IS NULL OR IsParticipator=@IsParticipator)
		AND 
		(@AddDate IS NULL OR AddDate=@AddDate)
		AND 
		(@SignInTime IS NULL OR SignInTime=@SignInTime)
		AND 
		(@FullName IS NULL OR FullName Like @FullName)
		AND 
		(@Avatar IS NULL OR Avatar Like @Avatar)
		AND 
		(@Company IS NULL OR Company Like @Company)
		AND 
		(@Department IS NULL OR Department Like @Department)
		AND 
		(@Phone IS NULL OR Phone Like @Phone)
		AND 
		(@AddUser IS NULL OR AddUser Like @AddUser)
		 
		SET @SumCount=@@RowCount
		IF(@SumCount%@PageSize=0)
		BEGIN
			SET @TotalPage=@SumCount/@PageSize
		END
		ELSE
		BEGIN
			SET @TotalPage=Round(@SumCount/@PageSize,0)+1
		END

		SELECT TOP (@PageSize) Identifier,IsParticipator,AddDate,SignInTime,FullName,Avatar,Company,Department,Phone,AddUser,@TotalPage as totalPage,@SumCount as SumCount
		 FROM #temp 
		WHERE
		 Identifier NOT IN (Select Top (@PageSize*(@CurrentPage-1))Identifier FROM #temp) 
	END
	ELSE
	BEGIN
	 	IF @Identifier>0 
		BEGIN
		SELECT 
		Identifier,IsParticipator,AddDate,SignInTime,FullName,Avatar,Company,Department,Phone,AddUser 
		FROM LotteryPerson 
		WHERE Identifier=@Identifier
		 END
		ELSE
		BEGIN 
		SELECT Identifier,IsParticipator,AddDate,SignInTime,FullName,Avatar,Company,Department,Phone,AddUser 
		FROM LotteryPerson 
		WHERE 1=1 AND 
		(@Identifier IS NULL OR Identifier=@Identifier)
		AND 
		(@IsParticipator IS NULL OR IsParticipator=@IsParticipator)
		AND 
		(@AddDate IS NULL OR AddDate=@AddDate)
		AND 
		(@SignInTime IS NULL OR SignInTime=@SignInTime)
		AND 
		(@FullName IS NULL OR FullName Like @FullName)
		AND 
		(@Avatar IS NULL OR Avatar Like @Avatar)
		AND 
		(@Company IS NULL OR Company Like @Company)
		AND 
		(@Department IS NULL OR Department Like @Department)
		AND 
		(@Phone IS NULL OR Phone Like @Phone)
		AND 
		(@AddUser IS NULL OR AddUser Like @AddUser)
		  
		END
	END
GO
/****** Object:  StoredProcedure [dbo].[LotteryPersonUpdate]    Script Date: 2024-11-06 14:04:07 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
 CREATE PROCEDURE [dbo].[LotteryPersonUpdate] 
	@Identifier int=NULL,
	@IsParticipator int=NULL,
	@AddDate datetime=NULL,
	@SignInTime datetime=NULL,
	@FullName nvarchar(50)=NULL,
	@Avatar nvarchar(MAX)=NULL,
	@Company nvarchar(50)=NULL,
	@Department nvarchar(50)=NULL,
	@Phone nvarchar(50)=NULL,
	@AddUser nvarchar(50)=NULL 
 AS 
 BEGIN 
 	UPDATE LotteryPerson
 	SET 
	
	IsParticipator=@IsParticipator,
	AddDate=@AddDate,
	SignInTime=@SignInTime,
	FullName=@FullName,
	Avatar=@Avatar,
	Company=@Company,
	Department=@Department,
	Phone=@Phone,
	AddUser=@AddUser
 	WHERE Identifier=@Identifier
 return @Identifier 
	END
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Id' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'AdminUser', @level2type=N'COLUMN',@level2name=N'Identifier'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'管理员账号' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'AdminUser', @level2type=N'COLUMN',@level2name=N'AdminName'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'管理员密码' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'AdminUser', @level2type=N'COLUMN',@level2name=N'AdminPwd'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'ID' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'LotteryPerson', @level2type=N'COLUMN',@level2name=N'Identifier'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'参与者姓名' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'LotteryPerson', @level2type=N'COLUMN',@level2name=N'FullName'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'头像图片' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'LotteryPerson', @level2type=N'COLUMN',@level2name=N'Avatar'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'公司' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'LotteryPerson', @level2type=N'COLUMN',@level2name=N'Company'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'部门' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'LotteryPerson', @level2type=N'COLUMN',@level2name=N'Department'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'电话号码' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'LotteryPerson', @level2type=N'COLUMN',@level2name=N'Phone'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'是否参与 默认是不参与=2 参与=1' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'LotteryPerson', @level2type=N'COLUMN',@level2name=N'IsParticipator'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'添加时间' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'LotteryPerson', @level2type=N'COLUMN',@level2name=N'AddDate'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'添加用户' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'LotteryPerson', @level2type=N'COLUMN',@level2name=N'AddUser'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'签到时间' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'LotteryPerson', @level2type=N'COLUMN',@level2name=N'SignInTime'
GO
